#!/usr/bin/env python
# encoding: utf-8
import re
import openpyxl
'''
excel使用openpyxl
pip install openpyxl
https://blog.csdn.net/weixin_41546513/article/details/109555832
'''



# UPDATE bim_user SET `name`='嘉靖不上朝',nickname='万历帝爷爷',origin_remark='红河培训-大明王朝有限公司' WHERE phone='13518705742'
def parse_excel():
    file_end = open('user003.sql', 'w', encoding='utf-8')
    # workbook = openpyxl.load_workbook('红河州工程建设标准化协会——2022年度会员培训报名.xlsx')
    # workbook = openpyxl.load_workbook('红河报名001.xlsx')
    # workbook = openpyxl.load_workbook('红河报名002.xlsx')
    workbook = openpyxl.load_workbook('今天开课名单4月28日(1).xlsx')
    worksheet = workbook['Sheet1']
    row_num = 0
    #行
    for row in worksheet.rows:
        row_num = row_num + 1
        # 表头
        # if row_num == 1:
        #     continue
        if row_num == 1 or row_num == 2:
            continue
        cell_num = 0
        name = ''
        nickname=''
        origin_remark=''
        phone=''
        #列
        for cell in row:
            cell_num = cell_num + 1
            # 手机
            if cell_num == 4:
                # print(str(cell.value))
                phone = str(cell.value)
            # 用户信息
            if cell_num == 3:
                # print(str(cell.value))
                name = str(cell.value)
                nickname = str(cell.value)
            if cell_num == 2:
                # print(str(cell.value))
                origin_remark = str(cell.value)
            # if cell_num == 3:
            #     # print(str(cell.value).split("，"))
            #     l = (cell.value).split("，")
            #     name = l[1]
            #     nickname =  l[1]
            #     origin_remark = l[0]
        sql = "UPDATE bim_user SET `name`='{0}',nickname='{1}',origin_remark='红河培训-{2}' WHERE phone='{3}'".format(name,nickname,origin_remark,phone)
        # print(sql)
        file_end.write(sql)
        file_end.write(";\n")

if __name__ == '__main__':
    parse_excel()
